package net.common.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;

import net.common.manager.ResourceManager;

/**
 * 数据库连接池
 * 
 * @author 孙树林
 * 
 */
public class ConnectionPool {

	private String jdbcDriver; // 数据库驱动
	private String jdbcUrl; // db连接
	private String username; // 用户名
	private String password; // 密码
	private int maxPoolCount; // 最大连接数
	private Vector<Connection> connectionPool = null; // 可用的数据库连接
	private Hashtable<Connection, Connection> hashUsingConnection = null; // 在使用的数据库连接
	private Vector<Connection> logConnectionPool = null; // 日志可用的数据库连接
	private Hashtable<Connection, Connection> logHashUsingConnection = null; // 日志在使用的数据库连接

	/**
	 * 构造器
	 * 
	 * @param strJdbcDriver
	 * @param strJdbcURL
	 * @param strUserName
	 * @param strPassword
	 * @param maxPoolCount
	 */
	private ConnectionPool(String jdbcDriver, String jdbcUrl, String username, String password, int maxPoolCount) {
		this.jdbcDriver = jdbcDriver;
		try {
			Class.forName(this.jdbcDriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		this.jdbcUrl = jdbcUrl;
		this.username = username;
		this.password = password;
		this.maxPoolCount = maxPoolCount;
		connectionPool = new Vector<Connection>();
		hashUsingConnection = new Hashtable<Connection, Connection>();
		logConnectionPool = new Vector<Connection>();
		logHashUsingConnection = new Hashtable<Connection, Connection>();
	}

	/**
	 * 创建数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection createConnection() throws SQLException {
		Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
		conn.setAutoCommit(false);
		return conn;
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 * @throws java.sql.SQLException
	 */
	public Connection getConnection() throws java.sql.SQLException {
		return getConnection(false);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @param isLog
	 *            isLog为true创建日志连接
	 * @return
	 * @throws java.sql.SQLException
	 */
	public Connection getConnection(boolean isLog) throws java.sql.SQLException {
		Connection conn = null;
		if (!isLog) {
			conn = getConnectionPool(); // 如果不创建LOG数据库连接
		} else {
			conn = getConnectionLogPool(); // 日志数据库连接
		}
		return conn;
	}

	/**
	 * 从数据连接池获得一个连接，如果不存在创建连接
	 * 
	 * @return
	 * @throws java.sql.SQLException
	 */
	private Connection getConnectionPool() throws java.sql.SQLException {
		Connection conn = null;
		synchronized (connectionPool) {
			if (connectionPool.size() > 0) {
				// 从空闲池获得数据连接
				conn = (Connection) connectionPool.firstElement();
				connectionPool.remove(conn);
			}
		}
		if (conn == null) {
			// 连接为空，创建连接
			conn = createConnection();
		} else if (conn.isClosed()) {
			// 连接关闭,使用连接池删除,创建新的连接
			hashUsingConnection.remove(conn);
			conn = createConnection();
		}
		conn = new ConnectionWrapper(conn); // 数据库连接包装
		hashUsingConnection.put(conn, conn); // 连接放入使用池
		conn.setAutoCommit(false); // 事物
		return conn;
	}

	/**
	 * 获得日志数据连接
	 * 
	 * @return
	 * @throws java.sql.SQLException
	 */
	private Connection getConnectionLogPool() throws java.sql.SQLException {
		Connection conn = null;
		synchronized (logConnectionPool) {
			if (logConnectionPool.size() > 0) {
				// 从空闲池获得数据连接
				conn = (Connection) logConnectionPool.firstElement();
				logConnectionPool.remove(conn);
			}
		}
		if (conn == null) {
			// 连接为空，创建连接
			conn = createConnection();
		} else if (conn.isClosed()) {
			// 连接关闭,使用连接池删除,创建新的连接
			logHashUsingConnection.remove(conn);
			conn = createConnection();
		}
		logHashUsingConnection.put(conn, conn); // 连接放入使用池
		conn.setAutoCommit(false); // 事物
		return conn;
	}

	/**
	 * 释放数据库连接
	 * 
	 * @param conn
	 * @throws java.sql.SQLException
	 */
	public void releaseConnection(Connection conn) throws java.sql.SQLException {
		releaseConnection(conn, true);
	}

	/**
	 * 释放数据库连接，并且关闭事务
	 * 
	 * @param conn
	 * @param isCommit
	 *            为真是，提交事务，为假时，回滚事务
	 * @throws java.sql.SQLException
	 */
	public void releaseConnection(Connection conn, boolean isCommit) throws java.sql.SQLException {
		if (isCommit) {
			conn.commit();
		} else {
			conn.rollback();
		}
		hashUsingConnection.remove(conn);
		if (hashUsingConnection.size() + connectionPool.size() < maxPoolCount) {
			// 使用连接数+空闲连接数小于最大连接数
			synchronized (connectionPool) {
				connectionPool.add(conn);
			}
		} else {
			// 关闭连接
			conn.close();
		}
	}

	/**
	 * 释放数据库连接，并且回滚事务
	 * 
	 * @param conn
	 * @throws java.sql.SQLException
	 */
	public void destroyConnection(Connection conn) throws java.sql.SQLException {
		conn.rollback();
		hashUsingConnection.remove(conn);
		conn.close();
	}

	/**
	 * 释放日志数据库连接，并且关闭事务
	 * 
	 * @param conn
	 * @param isCommit
	 *            为真是，提交事务，为假时，回滚事务
	 * @throws java.sql.SQLException
	 */
	public void releaseConnection(Connection conn, boolean isCommit, boolean bDebug) throws java.sql.SQLException {
		if (isCommit) {
			conn.commit();
		} else {
			conn.rollback();
		}
		logHashUsingConnection.remove(conn);
		if (logHashUsingConnection.size() + logConnectionPool.size() < maxPoolCount) {
			synchronized (logConnectionPool) {
				logConnectionPool.add(conn);
			}
		} else {
			conn.close();
		}
	}

	/**
	 * 释放数据库连接，并且回滚事务
	 * 
	 * @param conn
	 * @throws java.sql.SQLException
	 */
	public void destroyConnection(Connection conn, boolean bDebug) throws java.sql.SQLException {
		if (conn != null) {
			conn.rollback();
			logHashUsingConnection.remove(conn);
			conn.close();
		}
	}

	/**
	 * 释放所有数据库连接
	 * 
	 */
	public void destroyAll() {
		try {
			for (int i = 0; i < logConnectionPool.size(); i++) {
				Connection conn = (Connection) logConnectionPool.get(i);
				conn.close();
			}
			for (int i = 0; i < connectionPool.size(); i++) {
				Connection conn = (Connection) connectionPool.get(i);
				conn.close();
			}
			Collection<Connection> col = hashUsingConnection.values();
			Iterator<Connection> itr = col.iterator();
			while (itr.hasNext()) {
				Connection conn = (Connection) itr.next();
				conn.rollback();
				conn.close();
			}
			Collection<Connection> colLog = logHashUsingConnection.values();
			Iterator<Connection> itrLog = colLog.iterator();
			while (itrLog.hasNext()) {
				Connection conn = (Connection) itrLog.next();
				conn.rollback();
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 连接池实例
	 * 
	 * @return
	 */
	public static synchronized ConnectionPool getInstance() {
		ResourceManager resource = ResourceManager.getInstance();
		ConnectionPool connectionPool = new ConnectionPool(resource.getString("db.jdbcDriver"), resource.getString("db.jdbcUrl"), resource.getString("db.username"), resource
				.getString("db.password"), resource.getInt("db.maxPoolCount"));
		return connectionPool;
	}
}
